using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Common;
using Garlic.CC.Utility;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

using Garlic.DM.DataModel;

namespace Garlic.DAL
{
    /// <summary>
    /// DataAccess
    /// </summary>
    public partial class RoleDAL
    {
        private const string GETROLESBYUSERID = " SELECT "+
											"role.role_id,"+
                                            "role.role_name," +
                                            "role.role_type," +
                                            "role.comments," +
                                            "role.create_user," +
                                            "role.create_time," +
                                            "role.modify_user," +
                                            "role.modify_time" +
                                            " from TS_BAS_Role role,TR_BAS_User_Role rUserRole,TS_BAS_User usr " +
                                            " where usr.User_id=:UserID and usr.User_id=rUserRole.User_id and role.Role_id=rUserRole.Role_id ";

        private const string GETROLESBYMENUID = " SELECT " +
                                            "role.role_id," +
                                            "role.role_name," +
                                            "role.role_type," +
                                            "role.comments," +
                                            "role.create_user," +
                                            "role.create_time," +
                                            "role.modify_user," +
                                            "role.modify_time" +
                                            " from TM_BAS_Menu menu,TS_BAS_Auth auth,TS_BAS_Role role "+
                                            "where menu.menu_id=:menuid and role.role_id=auth.role_id and auth.resource_id=menu.menu_id and auth.resource_type='Menu' ";

        private const string GETROLESBYACTIONID = " SELECT " +
                                            "role.role_id," +
                                            "role.role_name," +
                                            "role.role_type," +
                                            "role.comments," +
                                            "role.create_user," +
                                            "role.create_time," +
                                            "role.modify_user," +
                                            "role.modify_time" +
                                            " from TS_BAS_Action action,TS_BAS_Auth auth,TS_BAS_Role role " +
                                            "where action.action_id=:actionid and role.role_id=auth.role_id and auth.resource_id=action.action_id and auth.resource_type='Action' ";

        public static List<RoleInfo> GetRolesByUserID(int userID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(GETROLESBYUSERID);
            db.AddInParameter(dbCommand, "UserID", DbType.Int32, userID);

            return GetList(dbCommand, db);
        }

        /// <summary>
        /// Get data list
        /// </summary>
        /// <param name="sql">SQL statement</param>
        /// <returns>RoleInfo list </returns>
        public static List<RoleInfo> GetList(DbCommand dbCommand, Database db)
        {
            List<RoleInfo> list = new List<RoleInfo>();
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    list.Add(CreateRoleInfo(dr));
                }
            }

            return list;
        }

        public static void DeleteUserRelation(string roleids)
        {
            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                string msql = "delete TR_BAS_User_Role where role_id in ("+roleids+")";
                DbCommand dbCommand = db.GetSqlStringCommand(msql);
                //db.AddInParameter(dbCommand, "roleids", DbType.Int32, roleids);

                db.ExecuteNonQuery(dbCommand);
            }
            catch (System.Exception ex)
            {
                throw;
            }
        }

        public static void DeleteAuthByRoleID(string roleids)
        {
            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                string msql = "delete TS_BAS_Auth where role_id in (" + roleids + ")";
                DbCommand dbCommand = db.GetSqlStringCommand(msql);
                //db.AddInParameter(dbCommand, "roleids", DbType.String, roleids);

                db.ExecuteNonQuery(dbCommand);
            }
            catch (System.Exception ex)
            {
                throw;
            }
        }

        public static List<RoleInfo> GetRolesByMenuID(int menuID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(GETROLESBYMENUID);
            db.AddInParameter(dbCommand, "menuid", DbType.Int32, menuID);

            return GetList(dbCommand, db);
        }

        public static List<RoleInfo> GetRolesByActionID(int actionID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(GETROLESBYACTIONID);
            db.AddInParameter(dbCommand, "actionid", DbType.Int32, actionID);

            return GetList(dbCommand, db);
        }

        /// <summary>
        /// Delete
        /// </summary>
        /// <param name="role_id">TS_BAS_Role Number</param>
        /// <returns></returns>
        public static void Delete(string roleids)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(" DELETE FROM TS_BAS_Role" +
                                        " WHERE role_id in ("+roleids+")");
            //db.AddInParameter(dbCommand, "roleids", DbType.Int32, roleids);
            db.ExecuteNonQuery(dbCommand);
        }

        public static RoleInfo GetRoleByRoleName(string roleName)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(TS_BAS_ROLE_SELECT, " where role_name='" + roleName + "'"));
            //db.AddInParameter(dbCommand, "RoleNmae", DbType.String, roleName);


            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                if (dr.Read())
                {
                    return CreateRoleInfo(dr);
                }
                else
                    return null;
            }
        }
    }
}
